const Class = require("../models/Class");
const Student = require("../models/Student");
const { Op } = require("sequelize");
// const sequelize = require("../models/db");
const {
  filterProperty,
  pickerProperty,
  deepClone,
} = require("../utils/operateProperty");
const moment = require("moment");
const validate = require("validate.js");
const rule = {
  name: {
    presence: {
      allowEmpty: false,
    },
    type: "string",
    length: { minimum: 2, maximum: 10 },
  },
  birthday: {
    presence: {
      allowEmpty: false,
    },
    // 该配置项需要自己扩展parse和format方法
    datetime: {
      // 限制生日的格式与日期范围
      dateOnly: true, // 仅支持日期格式
      earliest: moment.utc().subtract(100, "years"), // 最早为100年前
      latest: moment.utc().subtract(18, "years"), // 最晚为18年前
    },
  },
  sex: {
    presence: true,
    type: "boolean",
  },
  tel: {
    presence: {
      allowEmpty: false,
    },
    type: "string",
    format: /^1[0-9]\d{9}$/,
  },
  classId: {
    presence: true,
    numericality: {
      onlyInteger: true,
      strict: true,
      greaterThanOrEqualTo: 1,
      lessThanOrEqualTo: 16,
    },
  },
};
class StudentService {
  /* constructor() {
    this.init();
  }
  init() {
    Student.findAll().then((studenties) => {
      if (studenties.length === 0) {
        this.addStudent({
          name: "李二",
          birthday: "599-01-23",
          sex: 1,
          tel: '15955557899',
          classId: 1,
        });
      }
    });
  } */
  addStudent(studentObj) {
    studentObj = pickerProperty(studentObj, Object.keys(rule));
    // const res = validate(studentObj, rule);
    // console.log(res);
    return this.validateStudentObj(studentObj, async (val) => {
      const student = await this.getStudentByTel(val);
      if (student) {
        return Promise.reject(["This student is exist"]);
      }
    })
      .then(() => {
        // 判断数据库中是否有管理员，如果没有，自动添加一个默认管理员
        Student.create(studentObj);
      })
      .then(() => {
        console.log("添加学生成功");
      });
  }
  updateStudent(id, studentObj) {
    // 验证id
    const res = validate.single(id, { presence: true, type: "integer" });
    if (res)
      return Promise.reject(["id can't be blank and must be of type integer"]);
    studentObj = pickerProperty(studentObj, Object.keys(rule));
    return this.getStudentById(id)
      .then((student) => {
        if (!student) {
          return Promise.reject(["This student is not exist"]);
        }
      })
      .then(() => {
        return Student.update(studentObj, {
          where: {
            id: id,
          },
        });
      })
      .then(() => {
        console.log("更新成功");
      });
  }
  deleteStudent(studentId) {
    // 验证id
    const res = validate.single(studentId, {
      presence: true,
      type: "integer",
    });
    if (res)
      return Promise.reject(["id can't be blank and must be of type integer"]);
    return Student.destroy({
      where: {
        id: studentId,
      },
    }).then(() => {
      console.log("删除成功");
    });
  }

  // 查询学生
  // 通过id查询学生
  async getStudentById(id) {
    // 验证id
    const res = validate.single(id, { presence: true, type: "integer" });
    if (res)
      return Promise.reject(["id can't be blank and must be of type integer"]);
    const student = await Student.findByPk(id);
    return filterProperty(JSON.parse(JSON.stringify(student)), [
      "createdAt",
      "updatedAt",
      "deletedAt",
    ]);
  }
  // 通过姓名模糊匹配分页查询学生列表
  async getStudentByName(name, page = 1, limit = 10) {
    // 验证name
    const res = validate.single(name, rule.name);
    if (res) return Promise.reject(["This name is invalid"]);
    const { count, rows } = await Student.findAndCountAll({
      where: {
        name: {
          [Op.like]: `%${name}%`,
        },
      },
      offset: (page - 1) * limit,
      limit: limit,
    });
    return {
      total: count,
      datas: JSON.parse(JSON.stringify(rows)).map((s) =>
        filterProperty(s, ["createdAt", "updatedAt", "deletedAt"])
      ),
    };
  }
  // 通过classId分页查询学生列表
  async getStudentByClassId(classId, page = 1, limit = 10) {
    // 验证classId
    const res = validate.single(classId, rule.classId);
    if (res) return Promise.reject(["This classId is invalid"]);
    const { count, rows } = await Student.findAndCountAll({
      where: {
        classId: classId,
      },
      offset: (page - 1) * limit,
      limit: limit,
    });
    return {
      total: count,
      datas: JSON.parse(JSON.stringify(rows)).map((s) =>
        filterProperty(s, ["createdAt", "updatedAt", "deletedAt"])
      ),
    };
  }

  // 分页查询学生列表
  async getStudents(page = 1, limit = 10) {
    const { count, rows } = await Student.findAndCountAll({
      offset: (page - 1) * limit,
      limit: limit,
    });
    return {
      total: count,
      datas: JSON.parse(JSON.stringify(rows)).map((s) =>
        filterProperty(s, ["createdAt", "updatedAt", "deletedAt"])
      ),
    };
  }

  // 通过电话查询学生
  async getStudentByTel(tel) {
    // 验证tel
    const res = validate.single(tel, rule.tel);
    if (res) return Promise.reject(["This tel is invalid"]);
    const student = await Student.findOne({
      where: {
        tel: tel,
      },
    });
    console.log('getStudentByTel',student);
    return filterProperty(JSON.parse(JSON.stringify(student)), [
      "createdAt",
      "updatedAt",
      "deletedAt",
    ]);
  }

  // 电话模糊匹配分页查询学生列表
  async getStudentByTelLike(tel, page = 1, limit = 10) {
    const { count, rows } = await Student.findAndCountAll({
      where: {
        tel: {
          [Op.like]: `%${tel}%`,
        },
      },
      offset: (page - 1) * limit,
      limit: limit,
    });
    return {
      total: count,
      datas: JSON.parse(JSON.stringify(rows)).map((s) =>
        filterProperty(s, ["createdAt", "updatedAt", "deletedAt"])
      ),
    };
  }

  // 查询年龄大于指定年龄的学生列表
  async getStudentByOverAge(age, page = 1, limit = 10) {
    const startDate = Date.now() - age * 365 * 24 * 60 * 60 * 1000;
    const { count, rows } = await Student.findAndCountAll({
      where: {
        birthday: {
          [Op.lt]: startDate,
        },
      },
      offset: (page - 1) * limit,
      limit: limit,
    });
    return {
      total: count,
      // datas: rows.map((s) =>
      //   filterProperty(s && s.dataValues, [
      //     "createdAt",
      //     "updatedAt",
      //     "deletedAt",
      //   ])
      // ),
      datas: JSON.parse(JSON.stringify(rows)).map((s) =>
        filterProperty(s, [
          "createdAt",
          "updatedAt",
          "deletedAt",
        ])
      ),
    };
  }
  // 查询年龄小于指定年龄的学生列表
  async getStudentByUnderAge(age, page = 1, limit = 10) {
    const startDate = moment().subtract(age, "years");
    const { count, rows } = await Student.findAndCountAll({
      where: {
        birthday: {
          [Op.gt]: startDate,
        },
      },
      offset: (page - 1) * limit,
      limit: limit,
    });
    return {
      total: count,
      // datas: rows.map((s) =>
      //   filterProperty(s && s.dataValues, [
      //     "createdAt",
      //     "updatedAt",
      //     "deletedAt",
      //   ])
      // ),
      datas: JSON.parse(JSON.stringify(rows)).map((s) =>
        filterProperty(s, [
          "createdAt",
          "updatedAt",
          "deletedAt",
        ])
      ),
    };
  }

  // 查询年龄在指定年龄区间的学生列表
  async getStudentByRangeAge(startAge, endAge, page = 1, limit = 10) {
    // 根据起始年龄计算起始日期
    const startDate = moment().subtract(endAge, "years");
    const endDate = moment().subtract(startAge, "years");
    const { count, rows } = await Student.findAndCountAll({
      attributes: ["id", "name", "sex", "birthday", "age", "tel", "classId"],
      where: {
        birthday: {
          [Op.between]: [startDate, endDate],
        },
      },
      offset: (page - 1) * limit,
      limit: limit,
    });
    // console.log(rows[0].get('age'));
    // console.log(rows[0].get("birthday"));
    return {
      total: count,
      // datas: rows.map((s) => {
      //   const { dataValues } =s
      //   return { ...dataValues, ...{ birthday: s.get("birthday"), age: s.get("age") } };
      // }),
      datas: JSON.parse(JSON.stringify(rows)).map((s) =>
        filterProperty(s, [
          "createdAt",
          "updatedAt",
          "deletedAt",
        ])
      ),
    };
  }

  // 联表查询(班级加学生)
  async getCompleteStudentInfoList(page = 1, limit = 10) {
    const { count, rows } = await Student.findAndCountAll({
      attributes: ["id", "name", "sex", "birthday", "age", "tel", "classId"],
      include: [
        {
          model: Class,
          attributes: ["name", "openDate"],
        },
      ],
      offset: (page - 1) * limit,
      limit: limit,
    });
    return {
      total: count,
      datas: JSON.parse(JSON.stringify(rows)).map((s) =>
        filterProperty(s, [
          "createdAt",
          "updatedAt",
          "deletedAt",
        ])
      ),
    };
    /* const total = await sequelize.query(
      `SELECT 
        s.*, c.name calssname,c.openDate 
       FROM 
       student s 
       INNER JOIN class c ON s.classId = c.id`,
      {
        type: sequelize.QueryTypes.SELECT,
      }
    );
    const res = await sequelize.query(
      `SELECT 
        s.*, c.name calssname,c.openDate 
       FROM 
       student s 
       INNER JOIN class c ON s.classId = c.id
       LIMIT ${(page - 1) * limit},${limit}`,
      {
        type: sequelize.QueryTypes.SELECT,
      }
    );
    // console.log(res);
    return {
      total: total.length,
      datas: res.map((s) =>
        filterProperty(s, ["createdAt", "updatedAt", "deletedAt"])
      ),
    }; */
  }

  validateStudentObj(obj, validator) {
    const rules = deepClone(rule);
    rules.tel.exist = {
      validator,
    };
    // console.log("rules", rules);
    return validate.async(obj, rules);
  }
}

const studentService = new StudentService();
module.exports = studentService;
